package roof_extractor;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.nio.ByteBuffer;
import java.nio.CharBuffer;
import java.nio.channels.FileChannel;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;

import org.w3c.dom.Document;
import org.w3c.dom.NodeList;
import org.w3c.tidy.Tidy;

public class Utils {
	
	public static void createSqliteDb(String db_abs_filepath) throws ClassNotFoundException,SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:" + db_abs_filepath);
		Statement stat = conn.createStatement();
		stat.executeUpdate( "create table annunci (Contratto, Tipologia, Indirizzo, " +
													"Latitudine, Longitudine, Locali, Superficie, Prezzo," +
													"Descrizione, Contatti)");
		conn.close();
	}
	
	public static void saveAnnuncio(String db_abs_filepath,Annuncio annuncio) throws ClassNotFoundException,SQLException {
		Class.forName("org.sqlite.JDBC");
		Connection conn = DriverManager.getConnection("jdbc:sqlite:" + db_abs_filepath);
		String sql = "insert into annunci (Contratto, Tipologia, Indirizzo, Latitudine, Longitudine, Locali, Superficie, Prezzo," +
						"Descrizione, Contatti) VALUES (?,?,?,?,?,?,?,?,?,?);";
		PreparedStatement stat = conn.prepareStatement(sql);
		stat.setString(1, annuncio.getContratto());
		stat.setString(2, annuncio.getTipologia());
		stat.setString(3, annuncio.getIndirizzo());
		stat.setString(4, annuncio.getLatitudine());
		stat.setString(5, annuncio.getLongitudine());
		stat.setString(6, annuncio.getLocali());
		stat.setString(7, annuncio.getSuperficie());
		stat.setString(8, annuncio.getPrezzo());
		stat.setString(9, annuncio.getDescrizione());
		stat.setString(10, annuncio.getContatti());
		stat.executeUpdate();
		
		conn.close();
	}
	
    public static CharSequence fromFile(String filename) throws IOException {
        FileInputStream input = new FileInputStream(filename);
        FileChannel channel = input.getChannel();
        ByteBuffer bbuf = channel.map(FileChannel.MapMode.READ_ONLY, 0, (int)channel.size());
        CharBuffer cbuf = Charset.forName("8859_1").newDecoder().decode(bbuf);
        return cbuf;

    }
    
	/**
	 * 
	 * @param filePath
	 *            the absolute path of the html saved file
	 * @param xpath_expr
	 *            the xpath expression to extract the data
	 * @return the NodeList identified by the given XPath
	 * @throws FileNotFoundException
	 * @throws XPathExpressionException
	 */
	public static NodeList getXPathFromFile(String xpath_expr, File file)
			throws FileNotFoundException, XPathExpressionException {

		FileInputStream html = new FileInputStream(file);
		Tidy tidy = new Tidy();
		tidy.setQuiet(true);
		tidy.setShowWarnings(false);
		Document page = tidy.parseDOM(html, null);
		XPathFactory xpf = XPathFactory.newInstance();
		XPath xpath = xpf.newXPath();
		NodeList nodes = (NodeList) xpath.evaluate(xpath_expr, page,XPathConstants.NODESET);		
		return nodes;

	}

}
